
ALTER TABLE dbo.Items ADD
	EdelO numeric(11, 2) NOT NULL CONSTRAINT DF_Items_EdelO DEFAULT 0
GO
COMMIT

USE [BSG]
GO
/****** Object:  View [dbo].[vw_Items]    Script Date: 12/05/2015 19:51:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_Items]
AS
SELECT     dbo.Items.GroupID, dbo.Groups.GroupCode, dbo.Groups.GroupName, dbo.Groups.GroupNameUrdu, dbo.Groups.Type, dbo.Items.ItemID, 
                      dbo.Items.ItemCode, dbo.Items.ItemName, dbo.Items.ItemNameUrdu, dbo.Items.Discontinue, dbo.Items.PRate, dbo.Items.SRate, 
                      dbo.Items.EdelO
FROM         dbo.Groups RIGHT OUTER JOIN
                      dbo.Items ON dbo.Groups.GroupID = dbo.Items.GroupID



USE [BSG]
GO
/****** Object:  View [dbo].[vw_Stock]    Script Date: 12/05/2015 19:51:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_Stock]
AS
SELECT     dbo.VW_Vouchers.SessionID, dbo.VW_Vouchers.VoucherNo, dbo.VW_Vouchers.VoucherDate, dbo.VW_Vouchers.BankAccountID AS PartyID, 
                      dbo.VW_Vouchers.BankAccountTitle AS PartyName, dbo.VW_Vouchers.AccountNo, dbo.VW_Vouchers.AccountTitle, dbo.vw_Items.GroupID, 
                      dbo.vw_Items.ItemID, dbo.vw_Items.ItemName, dbo.vw_Items.EdelO, SUM(ISNULL(dbo.VW_Vouchers.Qty, 0)) AS QTY, 
                      SUM(ISNULL(dbo.VW_Vouchers.Edel, 0)) AS Edel, SUM(ISNULL(dbo.VW_Vouchers.Erec, 0)) AS Erec
FROM         dbo.vw_Items LEFT OUTER JOIN
                      dbo.VW_Vouchers ON dbo.vw_Items.ItemID = dbo.VW_Vouchers.ItemID
WHERE     (dbo.VW_Vouchers.ItemID IS NOT NULL)
GROUP BY dbo.VW_Vouchers.BankAccountTitle, dbo.vw_Items.ItemName, dbo.VW_Vouchers.BankAccountID, dbo.VW_Vouchers.VoucherDate, 
                      dbo.vw_Items.ItemID, dbo.vw_Items.GroupID, dbo.VW_Vouchers.SessionID, dbo.VW_Vouchers.VoucherNo, dbo.VW_Vouchers.AccountNo, 
                      dbo.VW_Vouchers.AccountTitle, dbo.vw_Items.EdelO
HAVING      (SUM(ISNULL(dbo.VW_Vouchers.Edel, 0)) - SUM(ISNULL(dbo.VW_Vouchers.Erec, 0)) <> 0)
